var express = require('express');
var router = express.Router();
var sql = require('../db/index');

// 新增或者修改接口 
router.post('/saveOrUpdate', async function (req, res) {
  let { id, name, photo, product_category_id, description, price } = req.body;
  let mysql = '';
  // 判断id是否存在 存在修改 不存在新增
  if (id) {
    mysql = `update jz_product set name='${name}',
      photo='${photo}',description='${description}',product_category_id=${product_category_id},price='${price}' where id = ${id}
    `
  } else {
    mysql = `insert into jz_product (id,name,photo,status,description,product_category_id,price) values (null,'${name}','${photo}','正常','${description}',${product_category_id},${price})`
  }
  await sql.query(mysql);
  res.send({
    status: 200,
    message: '操作成功',
    data: null,
    timestamp: new Date().getTime()
  })
});

// 上架和下架接口 
router.post('/changeStatus', async function (req, res) {
  let { id, status } = req.body;
  if (status === '下架') {
    status = '下架'
  } else {
    status = '正常'
  }
  await sql.query(`update jz_product set status = '${status}' where id =${id}`);
  res.send({
    status: 200,
    message: status == '下架' ? '下架成功' : '上架成功',
    data: null,
    timestamp: new Date().getTime()
  })
})

// 分页模糊查询 
router.get('/pageQuery', async function (req, res) {
  let { page, pageSize, name, status, product_category_id } = req.query;
  page = +page;
  pageSize = +pageSize;
  product_category_id = +product_category_id;
  let result, result1;
  let offset = (page - 1) * pageSize;
  if (name && status && product_category_id) {
    let mysql = `select * from jz_product where name like '${'%' + name + '%'}' and status = '${status}' and product_category_id = ${product_category_id} limit ${pageSize} offset ${offset}`;
    result = await sql.query(mysql);
    let mysql1 = `select count(*) total from jz_product where name like '${'%' + name + '%'}' and status = '${status}' and product_category_id = ${product_category_id} limit ${pageSize} offset ${offset}`;
    result1 = await sql.query(mysql1);
  } else if (name && status) {
    let mysql = `select * from jz_product where name like '${'%' + name + '%'}' and status = '${status}' limit ${pageSize} offset ${offset}`;
    result = await sql.query(mysql);
    let mysql1 = `select count(*) total from jz_product where name like '${'%' + name + '%'}' and status = '${status}' limit ${pageSize} offset ${offset}`;
    result1 = await sql.query(mysql1);
  } else if (name && product_category_id) {
    let mysql = `select * from jz_product where name like '${'%' + name + '%'}' and product_category_id = ${product_category_id} limit ${pageSize} offset ${offset}`;
    result = await sql.query(mysql);
    let mysql1 = `select count(*) total from jz_product where name like '${'%' + name + '%'}' and product_category_id = ${product_category_id} limit ${pageSize} offset ${offset}`;
    result1 = await sql.query(mysql1);
  } else if (status && product_category_id) {
    let mysql = `select * from jz_product where status = '${status}' and product_category_id = ${product_category_id} limit ${pageSize} offset ${offset}`;
    result = await sql.query(mysql);
    let mysql1 = `select count(*) total from jz_product where status = '${status}'  and product_category_id = ${product_category_id} limit ${pageSize} offset ${offset}`;
    result1 = await sql.query(mysql1);
  } else if (name || status || product_category_id) {
    if (name) {
      let mysql = `select * from jz_product where name like '${'%' + name + '%'}' limit ${pageSize} offset ${offset}`;
      result = await sql.query(mysql);
      let mysql1 = `select count(*) total from jz_product where name like '${'%' + name + '%'}' limit ${pageSize} offset ${offset}`;
      result1 = await sql.query(mysql1);
    } else if (status) {
      let mysql = `select * from jz_product where status ='${status}' limit ${pageSize} offset ${offset}`;
      result = await sql.query(mysql);
      let mysql1 = `select count(*) total from jz_product where status = '${status}' limit ${pageSize} offset ${offset}`;
      result1 = await sql.query(mysql1);
    } else {
      let mysql = `select * from jz_product where product_category_id = ${product_category_id} limit ${pageSize} offset ${offset}`;
      result = await sql.query(mysql);
      let mysql1 = `select count(*) total from jz_product where product_category_id = ${product_category_id} limit ${pageSize} offset ${offset}`;
      result1 = await sql.query(mysql1);
    }
  } else {
    let mysql = `select * from jz_product  limit ${pageSize} offset ${offset}`;
    result = await sql.query(mysql);
    let mysql1 = `select count(*) total from jz_product  limit ${pageSize} offset ${offset}`;
    result1 = await sql.query(mysql1);
  }
  result = result.filter(item => {
    return item.length > 0;
  })
  console.log(result[0], '2222');
  total = result1[0][0].total;
  res.send({
    status: 200,
    message: '查询成功',
    data: result[0],
    total,
    timestamp: new Date().getTime()
  })
})


module.exports = router;